Mega Code Archive
System Tables Views
1) A procedure with dependencies
2) ALL_CATALOG view provides a list of objects that you have been granted access to, as well as those that you own
3) ALL_IND_COLUMNS view contains information about the columns indexed by an index on a table
4) ALL_INDEXES view contains information about the indexes
5) ALL_OBJECTS view combines all types of Oracle structures into one view
6) ALL_TAB_COLUMNS view contains information about the columns in all tables accessible
7) Alter table emp move tablespace users and then check the result by querying user_tables
8) Blocked-session details
9) CAT table
10) Check column type with user-defined type in sys col$
11) Check dependencies for procedure
12) Check for locks held after starting the transaction
13) Check if an object is valid by querying user_segments table
14) Check index column and contraints for just created table
15) Check locked objects
16) Check new created tables in user_objects
17) Check package reference
18) Check package status
19) Check query in user_indexes after creation
20) Check the status of the trigger
21) Check user dependencies for a given table
22) Check user name and password for users
23) Check user procedure status and text
24) Check user_types for just created types
25) Combine user_constraints and user_cons_columns table
26) Compute sum of bytes on pool
27) Create sql statements from user_tables
28) Create view on v$session
29) Data Dictionary Scope
30) Data dictionary views for valid and invalid
31) Decode column position
32) Decode column type and element type in dba_coll_types table
33) Determine if flashback query is enabled on your system
34) Determine what object privileges were available through roles granted him
35) Determining the Status of the Resource Plans
36) Did the command create index statistics
37) Display SGA area sizes
38) Display the text of a cached query in the shared pool, then the execution plan
39) Displaying the Account Status of All Users
40) Dynamic Performance Views
41) Find time zones for a given UTC offset
42) Finding, Validating, and Describing Packages
43) Get a complete list of all the data dictionary views
44) Get a list of constraints defined on the employee table from User_Constraints
45) Get ALL SYNONYMS
46) Get all system packages
47) Get all tables that you have been granted access to
48) Get all views
49) Get code for all procedure, function and package from user_objects
50) Get code from user_source table
51) Get DBA SYNONYMS
52) Get detailed error message from user_errors
53) Get global name
54) Get object id for created table
55) Get oracle installation path
56) Get owner, index_name and status for DBA_indexes
57) Get owner, table name and table space name from dba_tables
58) Get table name and table space for a table by its name
59) Get the log history information for the past 3 days
60) Get the name and type of object locked
61) Get user privilege from USER_SYS_PRIVS
62) Get user SYNONYMS
63) Getting a Database List
64) Getting information on sequences
65) If procedure is valid
66) If trigger exists, drop trigger
67) If view exists, drop view
68) IO (expressed in database blocks) by datafile
69) Join all_objects and all_users to list user and its object count
70) Join dba_users, dba_sys_privs
71) Join dba_users, dba_tab_privs
72) Join on and where clause
73) Join sys col$ and user_objects
74) Join user_constraints and user_cons_columns table
75) Join v$session, v$sql and v$open_cursor
76) List all not valid database objects
77) List all stored procedures
78) List all sys owned package name
79) List database parameters
80) List table, table partition, index, index partition and lob from dba_objects
81) List view text from user_views
82) Listing the Resource Consumer Groups
83) Lists the roles that have been granted to the user
84) Monitor for suspended transactions
85) Obtain retention parm
86) Output contraints name, column, rule
87) Output user constraints in details
88) Prints the total UGA for your environment
89) Privileges assigned to roles and available to the currently logged in user
90) Privileges available to the current user
91) Privileges granted to the user on objects in other schemas
92) Privileges made on columns of objects owned by the user
93) Privileges on columns of objects in other schemas granted to the user
94) Privileges that you have granted to others on tables in your schema
95) Query a table in user_table by table_name
96) Query a view in user_objects table for a view just created
97) Query all INVALID objects from user_objects table
98) Query all_arguments table
99) Query all_col_privs
100) Query all_objects table and subtract object_name
101) Query all_tab_columns table and decode the data_type, data_precision, nullable columns
102) Query all_tab_privs
103) Query all_tables for owner and table_name
104) Query cat for table name and type
105) Query column_name, hidden_column, data_type from DBA_TAB_COLS for a table name
106) Query Data file Information
107) Query DBA_AUDIT_OBJECT for Name of the affected object
108) Query DBA_AUDIT_OBJECT for Numeric code for the action
109) Query DBA_AUDIT_OBJECT for Operating system username used
110) Query DBA_AUDIT_OBJECT for Oracle username of the account used
111) Query DBA_AUDIT_OBJECT for Owner of the affected object
112) Query DBA_AUDIT_OBJECT for Terminal ID used
113) Query DBA_AUDIT_SESSION for Login time
114) Query DBA_AUDIT_SESSION for Logoff time
115) Query DBA_AUDIT_SESSION for Operating system username used
116) Query DBA_AUDIT_SESSION for Oracle username of the account used
117) Query DBA_AUDIT_SESSION for Terminal ID used
118) Query dba_col_privs
119) Query DBA_DATA_FILES
120) Query dba_ddl_locks table before and after recompile a procedure
121) Query dba_dependencies
122) Query dba_dependencies by owner and referenced_name
123) Query DBA_EXTENTS for Extent number in the segment, Starting block number for the extent, Size of the extent in bytes,
124) Query DBA_EXTENTS for Name of the segment, Type of segment (ex TABLE, INDEX)
125) Query DBA_EXTENTS for Tablespace name,Owner of the segment
126) Query dba_external_tables
127) Query dba_job table
128) Query dba_lobs table
129) Query dba_method_result
130) Query dba_object_tables
131) Query dba_objects table group by object_type
132) Query dba_outlines table
133) Query dba_procedure table
134) Query DBA_ROLE_PRIVS, ROLE_TAB_PRIVS for Name of the object, Privilege granted,Was admin option granted
135) Query DBA_ROLE_PRIVS, ROLE_TAB_PRIVS for Recipient of the grant,Owner of the object
136) Query dba_role_privs table
137) Query dba_roles
138) Query dba_source table
139) Query dba_synonyms and user_catalog table
140) Query dba_sys_privs table
141) Query dba_tab_comments
142) Query dba_tab_privs table to get table privilege
143) Query DBA_TAB_SUBPARTITIONS
144) Query dba_tables by owners
145) Query dba_tables for table info
146) Query dba_trigger table
147) Query dba_type_attributes table
148) Query dba_type_method table
149) Query dba_types table
150) Query dba_updatable_columns
151) Query dba_users table
152) Query dba_users table for default_tablespace and temporary_tablespace
153) Query dba_views table
154) Query dictionary table
155) Query global_name table
156) Query language characters in effect
157) Query log file information
158) Query object name by object id against dba_objects
159) Query object_type, object_name from user_objects
160) Query parameter for
161) Query process for The Current Usage of the PGA Memory
162) Query session_role table
163) Query SQL table
164) Query sys aux_stats$
165) Query sys col$ table with sub query
166) Query sys_nc_rowinfo$ from object table
167) Query table_name, tablespace_name from user_tables
168) Query the USER_TAB_COLUMNS view to get all columns for a table that you own
169) Query to Find Out the File and Block IDs
170) Query user granted roles
171) Query user_constraints for constriant name, type, table name
172) Query user_dependencies for cross references
173) Query user_indexes and user_ind_columns table
174) Query user_object_size table
175) Query user_objects for invalid package body
176) Query user_objects for invalid view
177) Query user_objects for trigger
178) Query user_objects in PLSQL
179) Query user_objects table
180) Query USER_OBJECTS table by object name
181) Query user_objects table for all procedure
182) Query user_objects table for stored procedure before and after recompile
183) Query user_outline_hints table
184) Query user_outline_hints with outline name
185) Query user_outlines table
186) Query user_sequences table
187) Query USER_SEQUENCES table to get the sequence info
188) Query user_source table for a stored procedure name and procedure type
189) Query user_tab_privs
190) Query user_tables for the table just created
191) Query user_triggers
192) Query user_triggers with trigger name
193) Query user_unused_col_tabs
194) Query USER_UPDATABLE_COLUMNS table
195) Query user_views
196) Query user-defined view info from USER_VIEWS table
197) Query user-objects for stored procedure
198) Query v$controlfile table
199) Query v$db_cache_advice for factor
200) Query v$db_object_cache table
201) Query v$dbfile table
202) Query v$lock, v$session table
203) Query V$OBJECT_USAGE
204) Query v$option table
205) Query V$PARAMETER for current parameter settings
206) Query v$parameter for database name
207) --Query v$parameter table for optimizer_mode
208) Query v$pga_target_Advice
209) Query v$process a, v$session
210) Query V$ROLLNAME, V$ROLLSTAT
211) Query V$RSRC_CONSUMER_GROUP to see what the resource usage among the consumer groups looks like
212) Query v$session table
213) Query v$session_event
214) Query v$sgastat
215) Query v$sql for sql text
216) Query v$sqlarea for sql_text
217) Query V$SYSSTAT for all cumulative system-level statistics
218) Query V$WAITSTAT for resource wait statistics
219) Querying the USER_TAB_PRIVS_MADE data dictionary view
220) Querying V_$FIXED_TABLE shows internal data dictionary structures
221) Read trigger name and Query all_Triggers table
222) Resource Limits for the Default Profile
223) Restore trigger create statement from user_trigger table
224) Search DBA_OBJECT_SIZE for package and procedure size
225) Search for indexes created by anyone other than the table owner
226) Searches for grants made by users other than the table owners
227) See which roles are available for you
228) Select Active_State from V$INSTANCE
229) Select banner from v$version
230) Select cluster_name, owner, table_name from dba_tables
231) Select column_name, comments from user_col_comments
232) Select Database_Status from V$INSTANCE
233) Select default_tablespace from user_users
234) Select distinct privilege from dba_sys_privs
235) SELECT JOB,WHAT,LAST_SEC,INTERVAL from USER_JOBS
236) Select name, block_size, current_size from v$buffer_pool
237) Select Name, Value, IsDefault from V$PARAMETER
238) Select segment_name, segment_type from user_segments
239) Select sql_text, hash_value, parsing_user_id, optimizer_mode from v$sql
240) Select statistics_name, system_status, statistics_view_name from v$statistics_level
241) Select table_name, block_size from dba_tables dt, dba_tablespaces dtbs
242) Select the default value from V$SESSION CLIENT_INFO
243) Selecting from the TAB view
244) Show parameter type and value from v$parameter
245) Show parameters block
246) Show the procedure is marked invalid
247) Show the status of a procedure
248) Show when the current user connected to the system (not available on all platforms)
249) Show when the current user was last active
250) SYS_EXTRACT_UTC(x)
251) Table and view from obj
252) The Current Usage of the SGA
253) To check how the total PGA is being allocated by the instance,query the V$PGASTAT dynamic view
254) To find out what procedures and functions you have created, use the following SQL query
255) To get a listing of all tables that you own
256) User and its average object id
257) User System Privileges
258) User Table Privileges report
259) USER_CATALOG view also has a synonym defined
260) User_indexes join user_ind_columns ic using (table_name,index_name)
261) User_jobs table
262) User_tables structure
263) Users in rollback segments
264) Using the DBA_TAB_COLUMNS View
265) V$INSTANCE view provides one row of statistics for each Oracle instance running against the database
266) V$instance instance_name
267) V$SESSION dynamic contains all the currently active users usernames and the SQL they are executing in the database
268) Verify the creation of the statistics by running the following queries
269) Verifying Resource Consumer Group Membership of Users
270) View comment on any tables
271) Viewing errors in the database
272) Viewing Executing Jobs
273) Viewing Your Own Jobs
274) Who referenced current users objects